import logging
from datetime import datetime
from typing import Optional, List

from fastapi import APIRouter, Query, Depends, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db, SessionLocal

equipment_info_router = APIRouter(prefix="/equipment/info", tags=["设备资料管理"])


class EquipmentInfoSearch(PaginationBase):
    fequipmentname: Optional[str] = Field(None, description="设备名称")
    fclassstno: Optional[str] = Field(None, description="设备类型")
    fequipmenttype: Optional[str] = Field(None, description="设备状态")
    fequipmentno: Optional[str] = Field(None, description="设备编号")


# todo 6.设备信息
@equipment_info_router.get("/list", summary="查询设备资料")
def list_equipment_info(search: EquipmentInfoSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from s_ts_equipment_info where 1=1"
    if search.fequipmentname is not None:
        sql = sql + " and fequipmentname like concat('%', :fequipmentname ,'%')"
        param["fequipmentname"] = search.fequipmentname
    if search.fclassstno is not None:
        sql = sql + " and fclassstno = :fclassstno "
        param["fclassstno"] = search.fclassstno
    if search.fequipmenttype is not None:
        sql = sql + " and fequipmenttype = :fequipmenttype "
        param["fequipmenttype"] = search.fequipmenttype
    if search.fequipmentno is not None:
        sql = sql + " and fequipmentno = :fequipmentno "
        param["fequipmentno"] = search.fequipmentno
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select feuipmentid,fequipmentno,fequipmentname,fclassstno,funit,fbrand,fspec,fuseyear,"
    sql = sql + " fimage,fequipmenttype,fpanel,fbarcode,fbuyprice,flowvoltage,fheightvoltage,felccurrent,"
    sql = sql + " fip,fport,fordday,fupddate,foperator,fcolor,net_weight,fcharge,fchargeprice"
    sql = sql + " from s_ts_equipment_info where 1=1"
    if search.fequipmentname is not None:
        sql = sql + " and fequipmentname like concat('%', :fequipmentname ,'%')"
        param["fequipmentname"] = search.fequipmentname
    if search.fclassstno is not None:
        sql = sql + " and fclassstno = :fclassstno "
        param["fclassstno"] = search.fclassstno
    if search.fequipmenttype is not None:
        sql = sql + " and fequipmenttype = :fequipmenttype "
        param["fequipmenttype"] = search.fequipmenttype
    if search.fequipmentno is not None:
        sql = sql + " and fequipmentno = :fequipmentno "
        param["fequipmentno"] = search.fequipmentno
    if search.order == "desc":
        sql = sql + " order by  fequipmenttype,fequipmentno desc "
    else:
        sql = sql + " order by  fequipmenttype,fequipmentno  "
    sql = sql + " limit  :limit  offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    # detailList 指令列表
    param = {}
    sql_3 = " select b.factionno,b.fdictno,b.faction,b.fdescribe,b.fimage,b.findex,b.scene_flag"
    sql_3 = sql_3 + " from s_ts_equipment_info a,s_ts_equipment_action b "
    sql_3 = sql_3 + " where a.feuipmentid=b.fdictno "
    if search.fequipmentname is not None:
        sql_3 = sql_3 + " and a.fequipmentname like concat('%', :fequipmentname ,'%')"
        param["fequipmentname"] = search.fequipmentname
    if search.fclassstno is not None:
        sql_3 = sql_3 + " and a.fclassstno = :fclassstno "
        param["fclassstno"] = search.fclassstno
    if search.fequipmenttype is not None:
        sql_3 = sql_3 + " and a.fequipmenttype = :fequipmenttype "
        param["fequipmenttype"] = search.fequipmenttype
    sql_3 = sql_3 + " order by b.fdictno,b.factionno"
    data_action = db.execute(text(sql_3), param).fetchall()

    # paraList 参数列表
    param = {}
    sql_4 = " select b.fentryid,b.feuipmentid,b.fline,b.fparafield,b.fparaname,b.fparadict,b.fparatype"
    sql_4 = sql_4 + " from s_ts_equipment_info a,s_ts_equipment_para b "
    sql_4 = sql_4 + " where a.feuipmentid=b.feuipmentid "
    if search.fequipmentname is not None:
        sql_4 = sql_4 + " and a.fequipmentname like concat('%', :fequipmentname ,'%')"
        param["fequipmentname"] = search.fequipmentname
    if search.fclassstno is not None:
        sql_4 = sql_4 + " and a.fclassstno = :fclassstno "
        param["fclassstno"] = search.fclassstno
    if search.fequipmenttype is not None:
        sql_4 = sql_4 + " and a.fequipmenttype = :fequipmenttype "
        param["fequipmenttype"] = search.fequipmenttype
    sql_4 = sql_4 + " order by b.feuipmentid,b.fentryid"
    data_para = db.execute(text(sql_4), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['feuipmentid'] = data[key][0]
        d1['fequipmentno'] = data[key][1]
        d1['fequipmentname'] = data[key][2]
        d1['fclassstno'] = data[key][3]
        d1['funit'] = data[key][4]
        d1['fbrand'] = data[key][5]
        d1['fspec'] = data[key][6]
        d1['fuseyear'] = data[key][7]
        d1['fimage'] = data[key][8]
        d1['fequipmenttype'] = data[key][9]
        d1['fpanel'] = data[key][10]
        d1['fbarcode'] = data[key][11]
        d1['fbuyprice'] = data[key][12]
        d1['flowvoltage'] = data[key][13]
        d1['fheightvoltage'] = data[key][14]
        d1['felccurrent'] = data[key][15]
        d1['fip'] = data[key][16]
        d1['fport'] = data[key][17]
        d1['fordday'] = data[key][18]
        d1['fupddate'] = data[key][19]
        d1['foperator'] = data[key][20]
        d1['fcolor'] = data[key][21]
        d1['netweight'] = data[key][22]
        d1['fcharge'] = data[key][23]
        d1['fchargeprice'] = data[key][24]
        feuipmentid = data[key][0]

        act_list = []
        for j1 in range(len(data_action)):
            if (str(data_action[j1][1]) == str(feuipmentid)):
                d2 = {}
                d2['factionno'] = data_action[key][0]
                d2['fdictno'] = data_action[key][1]
                d2['faction'] = data_action[key][2]
                d2['fdescribe'] = data_action[key][3]
                d2['fimage'] = data_action[key][4]
                d2['findex'] = data_action[key][5]
                d2['sceneflag'] = data_action[key][6]
                act_list.append(d2)

        para_list = []
        for j2 in range(len(data_para)):
            if (str(data_para[j2][1]) == str(feuipmentid)):
                d3 = {}
                d3['fentryid'] = data_para[j2][0]
                d3['feuipmentid'] = data_para[j2][1]
                d3['fline'] = data_para[j2][2]
                d3['fparafield'] = data_para[j2][3]
                d3['fparaname'] = data_para[j2][4]
                d3['fparadict'] = data_para[j2][5]
                d3['fparatype'] = data_para[j2][6]
                para_list.append(d3)

        d1["paraList"] = para_list
        d1["detailList"] = act_list
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class EquipmentInfoCreate(BaseModel):
    """设备资料创建模型"""
    fequipmentno: str = Field(..., description="设备编号")
    fequipmentname: str = Field(..., description="设备名称")
    fclassstno: str = Field(..., description="分类编号")
    fequipmenttype: int = Field(..., description="设备类型ID")
    fpanel: int = Field(..., description="面板类型（1:有，0:无）")
    foperator: str = Field(..., description="操作人")
    fupddate: datetime = Field(default=datetime.now(), description="更新日期")  # 格式:YYYY-MM-DD

    # 可选字段（默认值与接口逻辑一致）
    fbarcode: str = Field("", description="设备条码")
    fbrand: str = Field("", description="品牌")
    fspec: str = Field("", description="规格")
    funit: str = Field("", description="单位")
    fuseyear: int = Field(0, description="使用年限")
    felccurrent: str = Field("0", description="电流（A）")
    fheightvoltage: int = Field(0, description="高压（V）")
    flowvoltage: int = Field(0, description="低压（V）")
    fordday: int = Field(0, description="订购天数")
    netWeight: str = Field("0", description="净重（kg）")

    # 自动生成的单号（由业务层填充）
    feuipmentid: Optional[int] = Field(None, description="设备ID（自动生成）")


@equipment_info_router.post("/create", summary="新增设备资料")
def create_equipment_info(equip: EquipmentInfoCreate, db: Session = Depends(get_db)):
    try:
        d1 = {}
        with db.begin():
            # 先取单号
            feuipmentid = ""
            sql = "select nextval('s_ts_equipment_info_feuipmentid_seq') as last_value"
            res = db.execute(text(sql)).fetchall()
            feuipmentid = res[0][0]

            sql = (
                "insert into s_ts_equipment_info(fequipmentno,fequipmentname,fclassstno,funit,fbrand,fspec,fuseyear,fequipmenttype,fpanel,fbarcode,fbuyprice,flowvoltage,"
                "fheightvoltage,felccurrent,fordday,fupddate,foperator,fcharge,fchargeprice)")
            sql = sql + (
                " values( :fequipmentno , :fequipmentname , :fclassstno , :funit , :fbrand , :fspec , :fuseyear , :fequipmenttype ,"
                " :fpanel , :fbarcode ,0, :flowvoltage , :fheightvoltage , :felccurrent , :fordday , :fupddate , :foperator ,1,0)")

            db.execute(text(sql), {
                "fequipmentno": equip.fequipmentno,
                "fequipmentname": equip.fequipmentname,
                "fclassstno": equip.fclassstno,
                "funit": equip.funit,
                "fbrand": equip.fbrand,
                "fspec": equip.fspec,
                "fuseyear": equip.fuseyear,
                "fequipmenttype": equip.fequipmenttype,
                "fpanel": equip.fpanel,
                "fbarcode": equip.fbarcode,
                "flowvoltage": equip.flowvoltage,
                "fheightvoltage": equip.fheightvoltage,
                "felccurrent": equip.felccurrent,
                "fordday": equip.fordday,
                "fupddate": equip.fupddate,
                "foperator": equip.foperator,
            })

            # 返回
            detail_list = []
            paraList = []
            d1['fbarcode'] = equip.fbarcode
            d1['fbrand'] = equip.fbrand
            d1['fbuyprice'] = 0
            d1['fcharge'] = 0
            d1['fchargeprice'] = 0
            d1['fclassstno'] = equip.fclassstno
            d1['fcolor'] = None
            d1['felccurrent'] = equip.felccurrent
            d1['fequipmentname'] = equip.fequipmentname
            d1['fequipmentno'] = equip.fequipmentno
            d1['fequipmenttype'] = equip.fequipmenttype
            d1['feuipmentid'] = feuipmentid
            d1['fheightvoltage'] = equip.fheightvoltage
            d1['fimage'] = None
            d1['flowvoltage'] = equip.flowvoltage
            d1['fmacno'] = None
            d1['foperator'] = equip.foperator
            d1['fordday'] = equip.fordday
            d1['fpanel'] = equip.fpanel
            d1['fspec'] = equip.fspec
            d1['funit'] = equip.funit
            d1['fupddate'] = equip.fupddate
            d1['fuseyear'] = equip.fuseyear
            d1['netWeight'] = equip.netWeight
            d1["detailList"] = detail_list  # 设备指令
            d1["paraList"] = paraList
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class EquipmentDetailItem(BaseModel):
    """设备控制指令明细"""
    feuipmentid: int = Field(..., description="设备ID")
    faction: str = Field(..., description="操作类型")
    fdescribe: str = Field(..., description="描述")
    finstructions: str = Field(..., description="指令内容")
    fmark: str = Field(..., description="标记")
    fnumflag: int = Field(..., description="数字标志")
    fbegnum: str = Field(..., description="开始数值")
    fendnum: str = Field(..., description="结束数值")


class EquipmentCheckItem(BaseModel):
    """设备巡查指令明细"""
    feuipmentid: int = Field(..., description="设备ID")
    fline: int = Field(..., description="行号")
    fcheckitemno: str = Field(..., description="检查项编号")
    fcheckitemname: str = Field(..., description="检查项名称")
    fchecktypeno: str = Field(..., description="检查类型编号")
    fcheckflag: int = Field(..., description="检查标志")


class EquipmentParaItem(BaseModel):
    """设备参数列表明细"""
    feuipmentid: int = Field(..., description="设备ID")
    fline: int = Field(..., description="行号")
    fparafield: str = Field(..., description="参数名")
    fparaname: str = Field(..., description="参数描述")
    fparadict: str = Field(..., description="参数字典")
    fparatype: int = Field(..., description="参数类型")


class EquipmentInfoUpdate(BaseModel):
    """设备信息主表修改模型"""
    feuipmentid: int = Field(..., description="设备ID")
    feuipmentname: str = Field(..., description="设备名称")
    fequipmentno: str = Field(..., description="设备编号")
    fequipmenttype: int = Field(..., description="设备类型")
    fclassstno: str = Field(..., description="分类编号")
    foperator: int = Field(..., description="操作人")
    fupddate: str = Field(..., description="更新日期")
    fpanel: int = Field(..., description="面板类型")

    # 可选字段（有默认值）
    fbarcode: str = Field("", description="条码")
    fbrand: str = Field("", description="品牌")
    fspec: str = Field("", description="规格")
    funit: str = Field("", description="单位")
    fimage: str = Field("", description="图片路径")

    # 数值类型字段（默认值为"0"）
    felccurrent: str = Field("0", description="电流")
    fheightvoltage: str = Field("0", description="高压")
    flowvoltage: str = Field("0", description="低压")
    fordday: str = Field("0", description="检修周期")
    fuseyear: str = Field("0", description="使用年限")
    netWeight: str = Field("0", description="净重")
    fbuyprice: str = Field("0", description="购买价格")
    fcharge: str = Field("0", description="收费标准")
    fchargeprice: str = Field("0", description="收费价格")

    # 关联列表
    detailList: List[EquipmentDetailItem] = Field([], description="设备控制指令列表")
    details2: List[EquipmentCheckItem] = Field([], description="设备巡查指令列表")
    paraList: List[EquipmentParaItem] = Field([], description="设备参数列表")


@equipment_info_router.post("/update", summary="修改设备资料")
def update_equipment_info(equip: EquipmentInfoUpdate, db: Session = Depends(get_db)):
    try:
        feuipmentid = equip.feuipmentid
        with db.begin():
            sql = "delete from s_ts_equipment_detail where feuipmentid= :feuipmentid "
            db.execute(text(sql), {
                "feuipmentid": feuipmentid,
            })
            jsondata = equip.detailList
            for data in jsondata:
                sql = "insert into s_ts_equipment_detail(feuipmentid,faction,fdescribe,finstructions,fmark,fnumflag,fbegnum,fendnum)"
                sql = sql + " values( :feuipmentid , :faction , :fdescribe , :finstructions , :fmark , :fnumflag , :fbegnum , :fendnum )"
                db.execute(text(sql), {
                    "feuipmentid": feuipmentid,
                    "faction": data.faction,
                    "fdescribe": data.fdescribe,
                    "finstructions": data.finstructions,
                    "fmark": data.fmark,
                    "fnumflag": data.fnumflag,
                    "fbegnum": data.fbegnum,
                    "fendnum": data.fendnum,
                })
            checkdata = equip.details2
            sql = "delete from s_ts_equipment_checkitem where feuipmentid= :feuipmentid "
            db.execute(text(sql), {
                "feuipmentid": feuipmentid,
            })
            for data in checkdata:
                sql = "insert into s_ts_equipment_checkitem(feuipmentid,fline,fcheckitemno,fcheckitemname,fchecktypeno,fcheckflag)"
                sql = sql + " values( :feuipmentid , :fline , :fcheckitemno , :fcheckitemname , :fchecktypeno , :fcheckflag )"
                db.execute(text(sql), {
                    "feuipmentid": feuipmentid,
                    "fline": data.fline,
                    "fcheckitemno": data.fcheckitemno,
                    "fcheckitemname": data.fcheckitemname,
                    "fchecktypeno": data.fchecktypeno,
                    "fcheckflag": data.fcheckflag,
                })
            paradata = equip.paraList
            sql = "delete from s_ts_equipment_para where feuipmentid= :feuipmentid "
            db.execute(text(sql), {
                "feuipmentid": feuipmentid,
            })
            for data in paradata:
                sql = "insert into s_ts_equipment_para(feuipmentid,fline,fparafield,fparaname,fparadict,fparatype)"
                sql = sql + " values( :feuipmentid , :fline , :fparafield , :fparaname , :fparadict , :fparatype )"
                db.execute(text(sql), {
                    "feuipmentid": feuipmentid,
                    "fline": data.fline,
                    "fparafield": data.fparafield,
                    "fparaname": data.fparaname,
                    "fparadict": data.fparadict,
                    "fparatype": data.fparatype,
                })
            sql = "update s_ts_equipment_info "
            sql = sql + " set fequipmentname= :fequipmentname ,fbarcode= :fbarcode ,fbrand= :fbrand "
            sql = sql + ",fbuyprice= :fbuyprice ,fclassstno= :fclassstno ,felccurrent= :felccurrent "
            sql = sql + ",fheightvoltage= :fheightvoltage ,flowvoltage= :flowvoltage ,fordday= :fordday "
            sql = sql + ",fpanel= :fpanel ,fspec= :fspec ,funit= :funit "
            sql = sql + ",fuseyear= :fuseyear ,fimage= :fimage ,net_weight= :netWeight "
            sql = sql + ",fcharge= :fcharge ,fchargeprice= :fchargeprice "
            sql = sql + ",fupddate= :fupddate ,foperator= :foperator "
            sql = sql + " where feuipmentid= :feuipmentid "
            db.execute(text(sql), {
                "fequipmentname": equip.feuipmentname,
                "fbarcode": equip.fbarcode,
                "fbrand": equip.fbrand,
                "fbuyprice": equip.fbuyprice,
                "fclassstno": equip.fclassstno,
                "felccurrent": equip.felccurrent,
                "fheightvoltage": equip.fheightvoltage,
                "flowvoltage": equip.flowvoltage,
                "fordday": equip.fordday,
                "fpanel": equip.fpanel,
                "fspec": equip.fspec,
                "funit": equip.funit,
                "fuseyear": equip.fuseyear,
                "fimage": equip.fimage,
                "netWeight": equip.netWeight,
                "fcharge": equip.fcharge,
                "fchargeprice": equip.fchargeprice,
                "fupddate": equip.fupddate,
                "foperator": equip.foperator,
                "feuipmentid": equip.feuipmentid,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@equipment_info_router.post("/delete/{feuipmentid}", summary="删除设备资料")
def delete_equipment(feuipmentid: int = Path(description="资料编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) from s_tc_equipment_inv where feuipmentid= :feuipmentid "
            data = db.execute(text(sql), {
                "feuipmentid": feuipmentid,
            }).scalar()

            if data > 0:
                return BaseResponse.error(message="设备正在被使用，不能删除")
            else:
                sql = "delete from s_ts_equipment_dangerset where feuipmentid= :feuipmentid "
                db.execute(text(sql), {
                    "feuipmentid": feuipmentid
                })
                sql = "delete from s_ts_equipment_info where feuipmentid= :feuipmentid "
                db.execute(text(sql), {
                    "feuipmentid": feuipmentid
                })
                sql = "delete from s_ts_equipment_detail where feuipmentid= :feuipmentid "
                db.execute(text(sql), {
                    "feuipmentid": feuipmentid
                })
                sql = "delete from s_ts_equipment_fault where feuipmentid= :feuipmentid "
                db.execute(text(sql), {
                    "feuipmentid": feuipmentid
                })
                sql = "delete from s_ts_equipment_mmset where feuipmentid= :feuipmentid "
                db.execute(text(sql), {
                    "feuipmentid": feuipmentid
                })
                sql = "delete from s_ts_equipment_para where feuipmentid= :feuipmentid "
                db.execute(text(sql), {
                    "feuipmentid": feuipmentid
                })
                return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")

